<?php

namespace App\Command\ExportFile;

use App\Web\Model\Resume;
use PhpOffice\PhpSpreadsheet;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use EchoPHP\Facades\Log as Log;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

class ExcelExportHelper {
    private $templates = [];
    private $template;
    /**
     * @var PhpSpreadsheet\Spreadsheet
     */
    private $workbook;
    /**
     * @var Worksheet|null
     */
    private $resumeSheetTemplate;
    private $resumeTitleXfIndex;
    private $resumeItemXfIndex;
    /**
     * @var Worksheet|null
     */
    private $currentSheet;
    private $currentRow = 1;
    /**
     * @var Resume
     */
    private $resumeModel;

    public function __construct() {
        $this->templates[6] = [
            'path' => ROOT_PATH . '/app/Command/ExportFile/resource/list1.xls',
            'summaryCell' => 'A1',
            'range' => 'A3:W3',
            'fields' => [-1, -1, 'resumeid', 'cname', 'projectname', 'casestatus', 'casename', 'flowatinfo', 'expectarea', 'gender',
                'birthday', 'area', 'workyear', 'degree', 'school', 'major', 'phone', 'email', 'company', 'recentPosition',
                'currentAnnualSalary', 'expectAnnualSalary', 'updatetime', 'operator'], //前几个是占位符
            'resumeSheet' => [
                'indexHyperlinkColumn' => 2,
                'activeCell' => 'B1',
                'index' => '1',
                'title' => 'Resume',
                'groupTitleRange' => 'B3:C3',
                'groupItemRange' => 'B4:C4',
                'beginRow' => 3,
                'keyColumn' => 2,
                'valueColumn' => 3,
            ]
        ];
        $this->template = $this->templates[6];

        $this->resumeModel = new Resume();
    }

    private function internalExport(array $dataSet, int $totalCount, $withDetailSheets = false, $returnWriter = false, $extraParams = [], $projectContact = []) {
        try {
            $this->workbook = PhpSpreadsheet\IOFactory::load($this->template['path']);
            if ($withDetailSheets) {    //初始化简历详情页模板
                $this->resumeSheetTemplate = $this->workbook->getSheetByName($this->template['resumeSheet']['title'] ?? 'Resume');
                if (empty($this->resumeSheetTemplate)) $this->resumeSheetTemplate = $this->workbook->getSheet(1);
                if (is_string($this->template['resumeSheet']['groupTitleRange']))
                    $this->template['resumeSheet']['groupTitleRange'] = PhpSpreadsheet\Cell\Coordinate::rangeBoundaries($this->template['resumeSheet']['groupTitleRange']);
                if (is_string($this->template['resumeSheet']['groupItemRange']))
                    $this->template['resumeSheet']['groupItemRange'] = PhpSpreadsheet\Cell\Coordinate::rangeBoundaries($this->template['resumeSheet']['groupItemRange']);
                $this->resumeTitleXfIndex = $this->getXfIndexArray($this->resumeSheetTemplate, $this->template['resumeSheet']['groupTitleRange']);
                $this->resumeItemXfIndex = $this->getXfIndexArray($this->resumeSheetTemplate, $this->template['resumeSheet']['groupItemRange']);
            }

            $indexSheet = $this->workbook->getSheet(0);

            if (!isset($extraParams['caseresumeids'])) {
                $columnIndexToRemove = [];
                foreach ($this->template['fields'] as $i => $fieldName) {
                    if (in_array($fieldName, ['projectname', 'casestatus', 'casename', 'flowatinfo', 'updatetime', 'operator'])) {
                        $columnIndexToRemove[] = $i;
                        unset($this->template['fields'][$i]);
                    }
                }
                $this->template['fields'] = array_values($this->template['fields']);
                rsort($columnIndexToRemove);
                foreach ($columnIndexToRemove as $columnIndex) {
                    $indexSheet->removeColumnByIndex($columnIndex);
                }
            }

            //添加沟通表头列
            if ($projectContact) {
                $extraFields = array_merge(array_column($projectContact, 'showname'), ['评语']);
                $start = count($this->template['fields']) - 1;
                $startColumnIndex = count($this->template['fields']);
                $this->template['fields'] = array_merge($this->template['fields'], $extraFields);
                $styleArray = [
//                    'font' => [
//                        'bold' => true,
//                    ],
                    //居中
                    'alignment' => [
                        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                    ],
                ];

                foreach ($extraFields as $extraField) {
                    $start++;
                    $column = PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($start);
                    $indexSheet->setCellValueExplicitByColumnAndRow($start, 2, $extraField, DataType::TYPE_STRING);
                    $indexSheet->getColumnDimensionByColumn($start)->setWidth(20);
                    $indexSheet->getStyle($column . '3')->applyFromArray($styleArray);
                }
                $lastColumn = PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($start);
                $startColumn = PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($startColumnIndex);
                $this->template['range'] = 'A3:' . $lastColumn . '3';
                $indexSheet->mergeCells($startColumn . '1:' . $lastColumn . '1');
                $indexSheet->setCellValueByColumnAndRow($startColumnIndex, 1, '沟通表头');
                //复制表头样式
                $indexSheet->duplicateStyle($indexSheet->getStyle('E2'),
                    'X2:' . $lastColumn . '2');

                $styleArray = [
                    'borders' => [
                        //左边框
                        'left' => [
                            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_MEDIUM
                        ]
                    ]
                ];

                $indexSheet->getStyle('X1')->applyFromArray($styleArray);
                $indexSheet->getStyle('X2')->applyFromArray($styleArray);
                $indexSheet->getStyle('X3')->applyFromArray($styleArray);
            }
            //$activeCell = $indexSheet->getActiveCell();

            //填充数据内容
            $templateRowData = $indexSheet->rangeToArray($this->template['range'], null, false, false);
            $templateRangeCoordinates = PhpSpreadsheet\Cell\Coordinate::rangeBoundaries($this->template['range']);
            $firstColumn = PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($templateRangeCoordinates[0][0]);
            $currentRow = $firstRow = $templateRangeCoordinates[0][1];
            if (!$withDetailSheets) {
                $tmpXfIndex = $indexSheet->getCellByColumnAndRow($this->template['resumeSheet']['indexHyperlinkColumn'] + 1, $currentRow)->getXfIndex();    //TODO：若将此行内容内联入下一行，则会导致两个单元格内容联动，原因不明
                $indexSheet->getColumnDimensionByColumn($this->template['resumeSheet']['indexHyperlinkColumn'])->setXfIndex($tmpXfIndex);
            }
            $successCount = 0;
            foreach ($dataSet as &$data) {
                try {
                    $indexSheet->fromArray($templateRowData, null, $firstColumn . $currentRow);  //复制模板行内容（仅数据）
                    foreach ($this->template['fields'] as $i => $fieldName) {
                        if (isset($data[$fieldName]) && !empty($data[$fieldName])) {
                            //避免因为单元格以等号开头导致的导出失败
                            $indexSheet->setCellValueExplicitByColumnAndRow($i, $currentRow, $data[$fieldName], DataType::TYPE_STRING);
                        }
                    }
                    if ($withDetailSheets) {
                        //详情页不重复生成
                        if (isset($data['keycode']) && !isset($resumeIdMap[$data['keycode']])) {//通过人才管理导出的情况
                            $this->generateResumeSheet($data); //生成简历详情excel
                            $resumeIdMap[$data['resumeid']] = $data['title'];
                        } elseif (!isset($resumeIdMap[$data['resumeid']])) { //其他渠道进行导出
                            $this->generateResumeSheet($data); //生成简历详情excel
                            $resumeIdMap[$data['resumeid']] = $data['title'];
                        }
                        $indexSheet->getCellByColumnAndRow($this->template['resumeSheet']['indexHyperlinkColumn'], $currentRow)->getHyperlink()->setUrl(
                            "sheet://'" . preg_replace("/'/u", "''", $resumeIdMap[$data['resumeid']]) . "'!" . $this->template['resumeSheet']['activeCell']);
                    }
                    $currentRow++;
                    $successCount++;
                } catch (\Exception $e) {
                    Log::error(e_dump($e), ['resumeid' => $data['resumeid']], 'cmd');
                }
            }
            if (isset($this->template['resumeSheet']['index'])) $this->workbook->removeSheetByIndex($this->template['resumeSheet']['index']);

            //将每一列样式应用至模板以下的全部区域
            $lastColumnIndex = $templateRangeCoordinates[1][0];
            if ($lastColumnIndex > 100 && ($rightmost = $indexSheet->getHighestColumn($firstRow)) < $lastColumnIndex) $lastColumnIndex = $rightmost;
            $lastRow = $currentRow - 1;
            for ($columnIndex = $templateRangeCoordinates[0][0]; $columnIndex <= $lastColumnIndex; $columnIndex++) {
                $currentColumn = PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($columnIndex);
                $indexSheet->duplicateStyle($indexSheet->getStyle($currentColumn . $firstRow),
                    $currentColumn . $firstRow . ':' . $currentColumn . $lastRow);
            }

            $summary = '共有' . $totalCount . '人，导出' . $successCount . '人。';
            $indexSheet->setCellValueExplicit($this->template['summaryCell'], $summary, DataType::TYPE_STRING);


            //$indexSheet->setSelectedCell($activeCell);
            $saveAsXls = mb_substr($this->template['path'], -4) == '.xls';
            $writer = $saveAsXls ? new PhpSpreadsheet\Writer\Xls($this->workbook) : new PhpSpreadsheet\Writer\Xlsx($this->workbook);

            if ($returnWriter) {
                return ['writer' => $writer, 'ext' => $saveAsXls ? '.xls' : '.xlsx', 'successCount' => $successCount];
            }
            return response()->streamDownload(function () use ($writer) {
                $writer->save('php://output');
            }, '简历索引.' . ($saveAsXls ? 'xls' : 'xlsx'),
                ['Content-Type' => ($saveAsXls ? 'application/vnd.ms-excel' : 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') . '; charset=UTF-8']);
        } catch (\Exception $e) {
            Log::error(e_dump($e), [], 'cmd');
            if ($returnWriter) {
                return ['writer' => null, 'ext' => null, 'successCount' => 0];
            }
            return response()->json([
                'status' => false,
                'message' => '导出简历列表失败',
            ]);
        }
    }

    public function export(array $dataSet, $templateId, int $totalCount, $returnWriter = false, $withDetailSheets = false, $oprId = 0, $extraParams = [], $projectContact = []) {
        if (empty($templateId) || empty($this->templates[$templateId])) return response()->json([
            'status' => false,
            'message' => '无效导出模板',
        ]);
        $this->template = $this->templates[$templateId];

        if ($withDetailSheets) {
            $resumeIds = array_column($dataSet, 'resumeid');
            $this->resumeModel->arrValueToKey($resumeIds);
            $resumeDetailList = $this->resumeModel->getResumeInfo($resumeIds, $oprId);
        }
        foreach ($dataSet as $key => &$item) {
            if (!is_array($item)) {
                unset($dataSet[$key]);
                continue;
            }
            if (isset($resumeDetailList[$item['resumeid']])) {  //数据来源：Resume接口
                $item = $resumeDetailList[$item['resumeid']] + $item;
                $item['workyear'] = $item['workyearnum'];
            }
            if (!empty($item['resumeinfo'])) $item += $item['resumeinfo'];  //数据来源：s_manager表，重复键名以原有的为准
            $item['recentPosition'] = $item['resumeinfo']['position'] ?? $item['position'];
            if (!empty($item['mobilephone'])) $item['phone'] = $item['mobilephone'];
            if (!empty($item['currsalaryfrom'])) $item['currentAnnualSalary'] = $this->renderSalary('元', $item['currsalaryfrom'], $item['currsalaryto'] ?? null, 13);
            if (!empty($item['expsalaryfrom'])) $item['expectAnnualSalary'] = $this->renderSalary('元', $item['expsalaryfrom'], $item['expsalaryto'] ?? null, 13);
            if (!empty($item['birthday']) && strlen($item['birthday'] > 8) && strtotime($item['birthday']) !== false) $item['birthday'] = date('Y年', strtotime($item['birthday']));
            if (!empty($item['managerid'])) $item['jingyingId'] = $item['managerid'];
            if (!empty($item['accountid'])) $item['51jobId'] = $item['accountid'];
            if (!empty($item['tagaddername'])) $item['operator'] = $item['tagaddername'];
            if (!empty($item['tagadddate'])) $item['updatetime'] = $item['tagadddate'];
            if (empty($item['alreadydown']) || $item['alreadydown'] != 1) {
                $item['cname'] = $item['email'] = $item['phone'] = $item['address'] = $item['othercontacts'] = '下载后可见';
            }
            if (isset($item['contactcontent']) && $item['contactcontent']) {
                foreach ($item['contactcontent'] as $contact) {
                    $item[$contact['fieldname']] = $this->contactContentTranslate($contact['type'], $contact['value'] ?? null, $contact['unit']);
                }
            }
        }
        $this->resumeModel->hideFields($dataSet, $extraParams);
        foreach ($dataSet as &$item) {
            $item['title'] = $item['resumeid'];
            if (!empty($item['alreadydown']) && $item['alreadydown'] == 1 && !empty($item['cname'])) {
                $item['title'] .= '-' . mb_substr(trim(preg_replace('/[*:?[\]\/\\\\]+/u', ' ', $item['cname'])), 0, 10);
            }
        }
        return $this->internalExport($dataSet, $totalCount, $withDetailSheets, $returnWriter, $extraParams, $projectContact);
    }

    public function renderSalary($unit, $from, $to = null, $ratio = 1, $precision = -1) {
        if (empty($from)) return '';
        $text = round($from * $ratio, $precision);
        if (!empty($to) && $to > $from) $text .= ' - ' . round($to * $ratio, $precision);
        if (!empty($unit)) $text .= ' ' . $unit;
        return $text;
    }

    private function generateResumeSheet(&$data) {
        $this->currentSheet = $this->addResumeSheet($data['title']);
        $this->currentRow = $this->template['resumeSheet']['beginRow'];
        $struct = [
            '个人信息' => [
                '姓名' => $data['cname'] ?? '',
                '性别' => $data['gender'] ?? '',
                '出生年份' => $data['birthday'] ?? '',
                '工作年限' => $data['workyearnum'] ?? '',
                '手机' => $data['phone'] ?? '',
                '求职状态' => $data['current_situation'] ?? '',
                '邮箱' => $data['email'] ?? '',
                '居住地' => $data['area'] ?? '',
                '户口/国籍' => $data['hukou'] ?? '',
                '婚姻状态' => $data['married'] ?? '',
                '政治面貌' => $data['politic'] ?? '',
            ],
            '目前年收入' => [
                '目前年收入' => $data['currentsalaryinfo']['total'] ?? '',
                '基本工资' => $data['currentsalaryinfo']['basesalary'] ?? '',
                '补贴/津贴' => $data['currentsalaryinfo']['allowance'] ?? '',
                '奖金/佣金' => $data['currentsalaryinfo']['bonus'] ?? '',
                '股权收益' => $data['currentsalaryinfo']['stock'] ?? '',
            ],
            '求职意向' => [
                '期望薪资' => $data['expectsalary'] ?? '',
                '地点' => $data['expectarea'] ?? '',
                '职能' => $data['position'] ?? '',
                '行业' => $data['workindustry'] ?? '',
                '到岗时间' => $data['entrytime'] ?? '',
                '工作类型' => $data['seektype'] ?? '',
                '自我评价' => $data['selfintro'] ?? '',
            ],
        ];
        if (!empty($data['idcard'])) $struct['个人信息'][$data['idtype']] = $data['idcard'];
        if (!empty($data['contacttype']) && !empty($data['othercontacts'])) $struct['个人信息'][$data['contacttype']] = $data['othercontacts'];
        if (!empty($data['address'])) {
            $struct['个人信息']['家庭住址'] = $data['address'];
            if (!empty($data['zipcode'])) $struct['个人信息']['家庭住址'] .= ' (邮编：' . $data['zipcode'] . '）';
        }
        if (!empty($data['height'])) $struct['个人信息']['身高'] = $data['height'] . 'cm';

        if (!empty($data['workinfo']) && count($data['workinfo'])) {
            $section = [];
            foreach ($data['workinfo'] as $item) {
                $section = array_merge($section, [
                    [$item['time']['full'] . (empty($item['duty']) ? '' : ' -- ' . $item['duty'])],
                    [$item['company'] . (@intval($item['isoverseas'] ?? 0) ? ' (海外经历)' : '')],
                ]);
                $industryArr = [];
                if (!empty($item['workindustry'])) $industryArr[] = $item['workindustry'];
                if (!empty($item['companysize'])) $industryArr[] = $item['companysize'];
                if (!empty($item['companytype'])) $industryArr[] = $item['companytype'];
                if (!empty($item['worktype'])) $industryArr[] = $item['worktype'];
                if (!empty($item['salary'])) $industryArr[] = '薪资：' . $item['salary'];
                if (count($industryArr)) $section[] = [implode(' | ', $industryArr)];
                $extraInfoArr = [];
                if (!empty($item['reportperson'])) $extraInfoArr[] = '下属人数：' . $item['reportperson'];
                if (!empty($item['reportboss'])) $extraInfoArr[] = '汇报对象：' . $item['reportboss'];
                if (!empty($item['leavereason'])) $extraInfoArr[] = '离职原因：' . $item['leavereason'];
                $item['extrainfo'] = count($extraInfoArr) ? implode(' | ', $extraInfoArr) : '';
                if (count($extraInfoArr)) $section[] = [implode(' | ', $extraInfoArr)];
                $content = trim(($item['content'] ? '工作描述：' . $item['content'] : '') . ($item['score'] ? "\r\n主要业绩：" . $item['score'] : ''));
                if (!empty($content)) $section[] = $content;
            }
            $struct['工作经验'] = ['工作经验' => $section];
        }
        if (!empty($data['projectinfo']) && count($data['projectinfo'])) {
            $section = [];
            foreach ($data['projectinfo'] as $item) {
                $section = array_merge($section, [
                    [$item['time']['full'] . ' -- ' . $item['project']],
                    ['所属公司：' . $item['company']],
                ]);
                $content = trim(($item['content'] ? '项目描述：' . $item['content'] : '') . ($item['function'] ? "\r\n责任描述：" . $item['function'] : ''));
                if (!empty($content)) $section[] = $content;
            }
            $struct['项目经验'] = ['项目经验' => $section];
        }
        if (!empty($data['educationinfo']) && count($data['educationinfo'])) {
            $section = [];
            foreach ($data['educationinfo'] as $item) {
                $section = array_merge($section, [
                    [$item['time']['full'] . ' -- ' . $item['school'] . (@intval($item['isoverseas'] ?? 0) ? ' (海外经历)' : '')],
                    ($item['major'] ? $item['major'] . ' ' : '') . $item['degree'],
                ]);
                if (!empty($item['content'])) $section[] = $item['content'];
            }
            $struct['教育经历'] = ['教育经历' => $section];
        }
        $section = [];
        if (!empty($data['schoolaward']) && count($data['schoolaward'])) {
            $schoolAwards = [];
            foreach ($data['schoolaward'] as $item) {
                $schoolAwards[] = $item['time']['full'] . ' -- ' . $item['cbonusname'] . ' ' . $item['cbonusclass'];
            }
            if (count($schoolAwards)) $section['校内荣誉'] = $schoolAwards;
        }
        if (empty($section['校内荣誉']) && !empty($data['awardinfo'])) $section['校内荣誉'] = $data['awardinfo'];
        if (!empty($data['schooljob']) && count($data['schooljob'])) {
            $schoolJobs = [];
            foreach ($data['schooljob'] as $item) {
                $schoolJobs[] = [$item['time']['full'] . ' -- ' . $item['cname']];
                if (!empty(trim($item['cdescribe']))) $schoolJobs[] = $item['cdescribe'];
            }
            if (count($schoolJobs)) $section['校内职务'] = $schoolJobs;
        }
        if (count($section)) $struct['在校情况'] = $section;

        $section = [];
        if (!empty($data['itskill']) && count($data['itskill'])) {
            $skill = [];
            foreach ($data['itskill'] as $item) {
                $skill[] = $item['skilltext'] . ' ' . $item['ability'];
            }
            $section['技能/语言'] = $skill;
        }
        if (!empty($data['certification']) && count($data['certification'])) {
            $cert = [];
            foreach ($data['certification'] as $item) {
                $cert[] = $item['time']['full'] . ' -- ' . $item['certname'] . ' ' . $item['score'];
            }
            $section['证书'] = $cert;
        }
        if (!empty($data['train']) && count($data['train'])) {
            $train = [];
            foreach ($data['train'] as $item) {
                $train[] = [$item['time']['full'] . ' -- ' . $item['ctrainlesson']];
                $tmp = [];
                if (!empty(trim($item['ctrainname']))) $tmp[] = '培训机构：' . $item['ctrainname'];
                if (!empty(trim($item['ctrainplace']))) $tmp[] = '培训地点：' . $item['ctrainplace'];
                if (count($tmp)) $train[] = implode(' ', $tmp);
                if (!empty(trim($item['cdescribe']))) $train[] = $item['cdescribe'];
            }
            $section['培训经历'] = $train;
        }
        if (count($section)) $struct['技能特长'] = $section;

        $section = [];
        if (!empty($data['others']) && count($data['others'])) {
            $others = [];
            foreach ($data['others'] as $item) {
                $others = array_merge($others, [
                    [$item['inforname']],
                    $item['describe'],
                ]);
            }
            $section['其他'] = $others;
        }
        if (empty($data['others']) && empty($data['schoolaward'])) $section['其他'] = $data['otherinfo'] ?? '';

        $struct['附加信息'] = $section;
        //沟通记录
//        if (!empty($data['contactcontent']) && count($data['contactcontent'])) {
//            $section = [];
//            foreach ($data['contactcontent'] as $item) {
//                $tmpArr = [$item['fieldname'] =>
//                    $this->contactContentTranslate($item['type'],$item['value'] ?? null,$item['unit'])];
//                $section = $section+$tmpArr;
//            }
//            $struct['沟通记录'] = $section;
//        }
//var_dump($struct);exit();
        foreach ($struct as $title => $dict) {
            $this->printTitle($title);
            $this->printDict($dict);
        }
        $styleArray = [
            'borders' => [
                'outline' => [
                    'borderStyle' => $this->resumeSheetTemplate->getStyle($this->cell($this->template['resumeSheet']['groupItemRange'][0]))->getBorders()->getLeft()->getBorderStyle(),
                ],
            ],
        ];
        $targetRange = $this->range([[$this->template['resumeSheet']['groupItemRange'][0][0], $this->template['resumeSheet']['beginRow']], [$this->template['resumeSheet']['groupItemRange'][1][0], $this->currentRow - 1]]);
        $this->currentSheet->getStyle($targetRange)->applyFromArray($styleArray);
        $this->currentSheet->setSelectedCell($this->template['resumeSheet']['activeCell']);

        $this->currentSheet->getDefaultRowDimension()->setRowHeight(-1);
    }

    private function addResumeSheet($title) {
        $resumeSheet = clone $this->resumeSheetTemplate;
        $resumeSheet->setTitle($title);
        $this->workbook->addSheet($resumeSheet);
        return $resumeSheet;
    }

    private function printTitle(string $title, Worksheet $sheet = null) {
        if (empty($sheet)) $sheet = $this->currentSheet;
        try {
            $sheet->getCellByColumnAndRow($this->template['resumeSheet']['groupTitleRange'][0][0], $this->currentRow)->setValueExplicit($title, DataType::TYPE_STRING);
        } catch (PhpSpreadsheet\Exception $e) {
        }
        $targetRange = $this->range([[$this->template['resumeSheet']['groupTitleRange'][0][0], $this->currentRow], [$this->template['resumeSheet']['groupTitleRange'][1][0], $this->currentRow]]);
        $this->applyXfIndexArray($this->resumeTitleXfIndex, $targetRange, $sheet);
        $sheet->mergeCells($targetRange);
        $this->currentRow++;
    }

    private function printDict(array $data, Worksheet $sheet = null) {
        if (empty($sheet)) $sheet = $this->currentSheet;
        $rowBegin = $this->currentRow;
        $boldCellCoordinates = [];
        foreach ($data as $key => $value) {
            if (!is_array($value) || (count($value) == 1 && !is_array($value[0]))) $value = [$value];
            $groupBeginRow = $this->currentRow;
            if (!is_int($key) || $key > 10000) {
                try {
                    $sheet->getCellByColumnAndRow($this->template['resumeSheet']['keyColumn'], $this->currentRow)->setValueExplicit($key, DataType::TYPE_STRING);
                } catch (PhpSpreadsheet\Exception $e) {
                }
            }
            foreach ($value as $line) {
                $bold = false;
                if (is_array($line) && isset($line[0])) {
                    $line = $line[0];
                    $bold = true;
                }
                if (!is_string($line)) continue;
                $cell = $sheet->getCellByColumnAndRow($this->template['resumeSheet']['valueColumn'], $this->currentRow);
                try {
                    $cell->setValueExplicit($line, DataType::TYPE_STRING);
                } catch (PhpSpreadsheet\Exception $e) {
                }
                if ($bold) $boldCellCoordinates[] = $cell->getCoordinate();
                $this->currentRow++;
            }
            if (count($value) > 1) {
                try {
                    $sheet->mergeCellsByColumnAndRow($this->template['resumeSheet']['keyColumn'], $groupBeginRow, $this->template['resumeSheet']['keyColumn'], $this->currentRow - 1);
                } catch (PhpSpreadsheet\Exception $e) {
                }
            }
        }
        $targetRange = $this->range([[$this->template['resumeSheet']['groupItemRange'][0][0], $rowBegin], [$this->template['resumeSheet']['groupItemRange'][1][0], $this->currentRow - 1]]);
        $this->applyXfIndexArray($this->resumeItemXfIndex, $targetRange, $sheet);
        foreach ($boldCellCoordinates as $cellCoordinate) {
            $sheet->getStyle($cellCoordinate)->getFont()->setBold(true);
        }
    }

    private function getXfIndexArray(Worksheet $sheet, $range) {
        if (is_string($range)) $range = PhpSpreadsheet\Cell\Coordinate::rangeBoundaries($range);
        if (!is_array($range) || !is_array($range[0])) return null;
        $xfIndexArr = [];
        for ($row = $rowBegin = intval($range[0][1]); $row <= $range[1][1]; $row++) {
            $xfIndexArr[$row - $rowBegin] = [];
            for ($column = $columnBegin = $range[0][0]; $column <= $range[1][0]; $column++) {
                $cell = $sheet->getCellByColumnAndRow($column, $row, false);
                $xfIndexArr[$row - $rowBegin][$column - $columnBegin] = $cell ? $cell->getXfIndex() : null;
            }
        }
        return $xfIndexArr;
    }

    private function applyXfIndexArray(array $xfIndexArr, $range, Worksheet $sheet = null) {
        if (empty($xfIndexArr)) return;
        if (is_string($range)) $range = PhpSpreadsheet\Cell\Coordinate::rangeBoundaries($range);
        if (!is_array($range) || !is_array($range[0])) return null;
        if (empty($sheet)) $sheet = $this->currentSheet;
        $sourceRowSpan = count($xfIndexArr);
        $sourceColumnSpan = count($xfIndexArr[0]);
        for ($row = $rowBegin = intval($range[0][1]); $row <= $range[1][1]; $row++) {
            for ($column = $columnBegin = $range[0][0]; $column <= $range[1][0]; $column++) {
                if (!empty($xfIndexArr[($row - $rowBegin) % $sourceRowSpan][($column - $columnBegin) % $sourceColumnSpan]))
                    $sheet->getCellByColumnAndRow($column, $row)->setXfIndex($xfIndexArr[($row - $rowBegin) % $sourceRowSpan][($column - $columnBegin) % $sourceColumnSpan]);
            }
        }
    }

    protected function cell($cellCoordinateArr) {
        if (count($cellCoordinateArr) != 2) throw new \InvalidArgumentException('$cellCoordinateArr should be [Column Number, Row Number].');
        if (!is_numeric($row = $cellCoordinateArr[1])) throw new \InvalidArgumentException('Invalid row number.');
        $column = is_numeric($cellCoordinateArr[0]) ? PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($cellCoordinateArr[0]) : $cellCoordinateArr[0];
        if (!is_string($column)) throw new \InvalidArgumentException('Invalid column number.');
        return $column . $row;
    }

    protected function range($rangeCoordinateArr) {
        if (count($rangeCoordinateArr) != 2) throw new \InvalidArgumentException('$rangeCoordinateArr should be [Start Cell, End Cell].');
        $result = $this->cell($rangeCoordinateArr[0]);
        if ($rangeCoordinateArr[1] != $rangeCoordinateArr[0]) $result .= ':' . $this->cell($rangeCoordinateArr[1]);
        return $result;
    }

    /**
     * 简历沟通记录根据字段名进行翻译
     * @param $field
     * @param $value
     * @return mixed|string
     */
    protected function contactContentTranslate($type, $value, $unitType) {
        try {
            switch ($type) {
                case 0://评语
                    return $value;
                case 3://单选按钮组
                    if (empty($value[0])) {
                        return '';
                    }
                    return $value[0] . (empty($value[1]) ? '' : '(' . $value[1] . ')');
                case 4://下拉单选框
                    if (empty($value[0])) {
                        return '';
                    }
                    return $value[0] . (empty($value[1]) ? '' : '(' . $value[1] . ')');
                case 5://下拉复选框
                    $result = [];
                    if (!isset($value)) {
                        return '';
                    }
                    foreach ($value as $item) {
                        $result = array_merge($result, [$item]);
                    }
                    return implode(',', $result);
                case 6://复选框组
                    $result = [];
                    if (!isset($value)) {
                        return '';
                    }
                    foreach ($value as $item) {
                        $keyName = array_keys($item);
                        $result = array_merge($result, [$item[$keyName[0]] . (empty($item[$keyName[1]]) ? '' : '(' . $item[$keyName[1]] . ')')]);
                    }
                    return implode(',', $result);
                case 9://日期范围
                    if (empty($value[0]) && empty($value[1])) {
                        return '';
                    }
                    return $value[0] . ' -- ' . $value[1];
                case 11://数字范围
                    if (empty($value[0]) && empty($value[1])) {
                        return '';
                    }
                    return $value[0] . ' -- ' . $value[1] . ' ' . $unitType;
                case 12://短文本范围
                    if (empty($value[0]) && empty($value[1])) {
                        return '';
                    }
                    return $value[0] . ' -- ' . $value[1] . ' ' . $unitType;
                case 13://系统控件-当前薪资（月薪）
                    $tmp = 0;
                    foreach ($value as $item) {
                        if (empty($item)) {
                            $tmp++;
                        }
                    }
                    if ($tmp == count($value)) {
                        return '';
                    }
                    $total = 0;
                    foreach ($value as &$item) {
                        $item = $item == '' ? 0 : $item;
                        $total += $item;
                    }
                    unset($item);
                    return '年收入:' . $total . '元 基本工资:' . $value[0] . '元 补贴/津贴:' . $value[1] . '元 奖金/佣金:' . $value[2] . '元 股权收益:' . $value[3] . '元';
                case 14://系统控件-当前薪资（年薪）
                    $tmp = 0;
                    foreach ($value as $item) {
                        if (empty($item)) {
                            $tmp++;
                        }
                    }
                    if ($tmp == count($value)) {
                        return '';
                    }
                    $total = 0;
                    foreach ($value as &$item) {
                        $item = $item == '' ? 0 : $item;
                        $total += $item;
                    }
                    unset($item);
                    return '年收入:' . $total . '万元 基本工资:' . $value[0] . '万元 补贴/津贴:' . $value[1] . '万元 奖金/佣金:' . $value[2] . '万元 股权收益:' . $value[3] . '万元';
                case 15://系统控件-期望薪资
                    if ($value[0] != '') {
                        $type = $value[0] == 0 ? '年薪' : '月薪';
                        $unit = $value[0] == 0 ? '万元' : '元';
                        return $type . ' ' . $value[1] . $unit . '--' . $value[2] . $unit;
                    }
                    return '';
                case 16://系统控件-地区（省市）-单选
                    return $value[1];
                case 17://系统控件-地区（省市）-多选
                    if ($value[0] != '') {
                        return implode(',', array_unique(explode('+', $value[1])));
                    }
                    return '';
                case 18://系统控件-语言证书-多选
                    if (empty($value[0])) {
                        return '';
                    }
                    return implode(',', array_unique(explode('+', $value[1])));
                default:
                    return $value[0] . (empty($value[0]) ? '' : $unitType);
            }
        } catch (\Exception $exception) {
            Log::error('沟通记录翻译错误', [$type, $value, $exception->getMessage()]);
            return '';
        }
    }
}